﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using br.com.SistemaChamados.Model;
using br.com.SistemaChamados;
using br.com.SistemaChamados.Enum;

namespace br.com.SistemaChamados.DAO
{
    public class CallSubCategoryDAO
    {
        private static string SQL_INSERT_CALLSUBCATEGORY = " INSERT INTO TBL_CALLSUBCATEGORY (TBL_CALLSUBCATEGORYDESCRIPTION, TBL_CALLCATEGORYID) VALUES (@callsubcategorydescription, @callcategoryid) ";
        private static string SQL_UPDATE_CALLSUBCATEGORY = " UPDATE TBL_CALLSUBCATEGORY SET TBL_CALLSUBCATEGORYDESCRIPTION = @callsubcategorydescription, TBL_CALLCATEGORYID=@callcategoryid WHERE TBL_CALLSUBCATEGORYID = @callsubcategoryid ";
        private static string SQL_DELETE_CALLSUBCATEGORY = " DELETE FROM TBL_CALLSUBCATEGORY WHERE TBL_CALLSUBCATEGORYID = @callsubcategoryid ";
        private static string SQL_GET_CALLSUBCATEGORIES = " SELECT * FROM TBL_CALLSUBCATEGORY";
        private static string SQL_GET_CALLSUBCATEGORY = " SELECT * FROM TBL_CALLSUBCATEGORY WHERE TBL_CALLSUBCATEGORYID = @callsubcategoryid";
        private static string SQL_GET_CALLSUBCATEGORY_CALLCATEGORY = " SELECT * FROM TBL_CALLSUBCATEGORY WHERE TBL_CALLCATEGORYID = @callcategoryid";

        public static CallSubCategory getCallSubCategory(CallSubCategory callSubCategory)
        {
            IDbConnection conn = null;
            IDbCommand command = null;
            IDataReader reader = null;
            CallSubCategory callSubCategoryReturn = null;
            CallCategory callCategory = null;
            try
            {
                conn = Mapped.Connection();
                command = Mapped.Command(SQL_GET_CALLSUBCATEGORY, conn);
                command.Parameters.Add(Mapped.Parameter("@callsubcategoryid", callSubCategory.Id));
                reader = command.ExecuteReader(CommandBehavior.CloseConnection);
                while (reader.Read())
                {
                    callSubCategoryReturn = new CallSubCategory();
                    callCategory = new CallCategory();
                    callSubCategoryReturn.Id = callSubCategory.Id;
                    callSubCategoryReturn.Description = Convert.ToString(reader["TBL_CALLSUBCATEGORYDESCRIPTION"].ToString());
                    callCategory.Id = Convert.ToInt32(reader["TBL_CALLCATEGORYID"].ToString());
                    callSubCategoryReturn.CallCategory = callCategory;
                }
            }
            catch (Exception ex)
            {
                callSubCategoryReturn = null;
                throw ex;
            }
            finally
            {
                if (conn != null) conn.Close();
            }
            return callSubCategoryReturn;
        }

        public static List<CallSubCategory> getCallSubCategoriesByCallCategory(CallSubCategory callSubCategory)
        {
            List<CallSubCategory> list = new List<CallSubCategory>();
            IDbConnection conn = null;
            IDbCommand command = null;
            IDataReader reader = null;
            CallSubCategory callSubCategoryReturn = null;
            CallCategory callCategory = null;
            try
            {
                conn = Mapped.Connection();
                command = Mapped.Command(SQL_GET_CALLSUBCATEGORY_CALLCATEGORY, conn);
                command.Parameters.Add(Mapped.Parameter("@callcategoryid", callSubCategory.CallCategory.Id));
                reader = command.ExecuteReader(CommandBehavior.CloseConnection);
                while (reader.Read())
                {
                    callSubCategoryReturn = new CallSubCategory();
                    callCategory = new CallCategory();
                    callSubCategoryReturn.Id = Convert.ToInt32(reader["TBL_CALLSUBCATEGORYID"].ToString());
                    callSubCategoryReturn.Description = Convert.ToString(reader["TBL_CALLSUBCATEGORYDESCRIPTION"].ToString());
                    callCategory.Id = Convert.ToInt32(callSubCategory.CallCategory.Id);
                    callSubCategoryReturn.CallCategory = callCategory;
                    list.Add(callSubCategoryReturn);
                    callSubCategoryReturn = null;
                }
            }
            catch (Exception ex)
            {
                list = null;
                throw ex;
            }
            finally
            {
                if (conn != null) conn.Close();
            }
            return list;
        }

        public static List<CallSubCategory> getCallSubCategories()
        {
            List<CallSubCategory> list = new List<CallSubCategory>();
            IDbConnection conn = null;
            IDbCommand command = null;
            IDataReader reader = null;
            CallSubCategory callSubCategoryReturn = null;
            CallCategory callCategory = null;
            try
            {
                conn = Mapped.Connection();
                command = Mapped.Command(SQL_GET_CALLSUBCATEGORIES, conn);
                reader = command.ExecuteReader(CommandBehavior.CloseConnection);
                while (reader.Read())
                {
                    callSubCategoryReturn = new CallSubCategory();
                    callCategory = new CallCategory();
                    callSubCategoryReturn.Id = Convert.ToInt32(reader["TBL_CALLSUBCATEGORYID"].ToString());
                    callSubCategoryReturn.Description = Convert.ToString(reader["TBL_CALLSUBCATEGORYDESCRIPTION"].ToString());
                    callCategory.Id = Convert.ToInt32(reader["TBL_CALLCATEGORYID"].ToString());
                    callSubCategoryReturn.CallCategory = callCategory;
                    list.Add(callSubCategoryReturn);
                    callSubCategoryReturn = null;
                }
            }
            catch (Exception ex)
            {
                list = null;
                throw ex;
            }
            finally
            {
                if (conn != null) conn.Close();
            }
            return list;
        }

        public static EnumGenericState InsertCallSubCategory(CallSubCategory callSubCategory)
        {
            EnumGenericState stateTransaction = EnumGenericState.GENERIC_STATE_UNKNOW;
            IDbConnection conn = null;
            IDbCommand command = null;
            try
            {
                conn = Mapped.Connection();
                command = Mapped.Command(SQL_INSERT_CALLSUBCATEGORY, conn);
                command.Parameters.Add(Mapped.Parameter("@callsubcategorydescription", callSubCategory.Description));
                command.Parameters.Add(Mapped.Parameter("@callcategoryid", callSubCategory.CallCategory.Id));
                command.ExecuteNonQuery();
                stateTransaction = EnumGenericState.GENERIC_STATE_SUCCESS;
            }
            catch (Exception ex)
            {
                stateTransaction = EnumGenericState.GENERIC_STATE_FAILED;
                throw ex;
            }
            finally
            {
                if (conn != null) conn.Close();
            }

            return stateTransaction;

        }

        public static EnumGenericState UpdateCallSubCategory(CallSubCategory callSubCategory)
        {
            EnumGenericState stateTransaction = EnumGenericState.GENERIC_STATE_UNKNOW;
            IDbConnection conn = null;
            IDbCommand command = null;
            try
            {
                conn = Mapped.Connection();
                command = Mapped.Command(SQL_UPDATE_CALLSUBCATEGORY, conn);
                command.Parameters.Add(Mapped.Parameter("@callsubcategoryid", callSubCategory.Id));
                command.Parameters.Add(Mapped.Parameter("@callsubcategorydescription", callSubCategory.Description));
                command.Parameters.Add(Mapped.Parameter("@callcategoryid", callSubCategory.CallCategory.Id));
                command.ExecuteNonQuery();
                stateTransaction = EnumGenericState.GENERIC_STATE_SUCCESS;
            }
            catch (Exception ex)
            {
                stateTransaction = EnumGenericState.GENERIC_STATE_FAILED;
                throw ex;
            }
            finally
            {
                if (conn != null) conn.Close();
            }
            return stateTransaction;
        }

        public static EnumGenericState DeleteCallSubCategory(CallSubCategory callSubCategory)
        {
            EnumGenericState stateTransaction = EnumGenericState.GENERIC_STATE_UNKNOW;
            IDbConnection conn = null;
            IDbCommand command = null;
            try
            {
                conn = Mapped.Connection();
                command = Mapped.Command(SQL_DELETE_CALLSUBCATEGORY, conn);
                command.Parameters.Add(Mapped.Parameter("@callsubcategoryid", callSubCategory.Id));
                command.ExecuteNonQuery();
                stateTransaction = EnumGenericState.GENERIC_STATE_SUCCESS;
            }
            catch (Exception ex)
            {
                stateTransaction = EnumGenericState.GENERIC_STATE_FAILED;
                throw ex;
            }
            finally
            {
                if (conn != null) conn.Close();
            }
            return stateTransaction;
        }
        public CallSubCategoryDAO()
        {
            //
            // TODO: Add constructor logic here
            //
        }
    }
}